﻿namespace Home
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.IO;
    using System.Reflection;
    using System.Text;
    using System.Threading.Tasks;
    using System.Transactions;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Input;
    using CsvHelper;
    using CsvHelper.Configuration;
    using IsolationLevel = System.Transactions.IsolationLevel;

    public partial class ProductPage : Page
    {
        private static readonly RoutedUICommand generateDiamondDataCommand = new RoutedUICommand("Generate Diamond Data", "GenerateDiamondData", typeof(ProductPage));
        private static readonly RoutedUICommand generateDiamondFileCommand = new RoutedUICommand("Generate Diamond File", "GenerateDiamondFile", typeof(ProductPage));
        private static readonly ProductDescriptor productDiamondDescriptor = new ProductDescriptor()
        {
            FileNameTemplate = "product-diamond-{0}.csv",
            ImportTableName = "tc_product_diamond_import",
            GenerateProductsDataAsync = GenerateDiamondsDataAsync,
            GenerateProductsFile = GenerateDiamondsFile,
        };
        private static readonly IReadOnlyList<string> diamondTypes = new List<string>() { "White", "Colored" };
        private static readonly IReadOnlyList<string> diamondColoredColors = new List<string>() { "淡绿色", "黄色", "紫色", "粉色", "蓝色" };
        private static readonly IReadOnlyList<string> diamondCuts = new List<string>() { "EX", "VG", "G", "F", "P" };
        private static readonly IReadOnlyList<string> diamondClarities = new List<string>() { "FL", "IF", "VVS1", "VVS2", "VS1", "VS2", "SI1", "SI2", "I1", "I2", "I3" };
        private static readonly IReadOnlyList<string> diamondColors = new List<string>() { "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
        private static readonly IReadOnlyList<string> diamondPolishes = new List<string>() { "EX", "VG", "G", "F", "P" };
        private static readonly IReadOnlyList<string> diamondReports = new List<string>() { "GIA", "IGI", "EGL", "China" };
        private static readonly IReadOnlyList<string> diamondShapes = new List<string>() { "Round", "NonRound" };
        private static readonly IReadOnlyList<string> diamondSymmetries = new List<string>() { "EX", "VG", "G", "F", "P" };

        public ProductPage()
        {
            InitializeComponent();
        }

        public static RoutedUICommand GenerateDiamondDataCommand
        {
            get { return generateDiamondDataCommand; }
        }

        public static RoutedUICommand GenerateDiamondFileCommand
        {
            get { return generateDiamondFileCommand; }
        }

        private void Generate_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            switch ((string)e.Parameter)
            {
                case "DiamondData":
                    GenerateData(productDiamondDescriptor);
                    break;
                case "DiamondFile":
                    GenerateFile(productDiamondDescriptor);
                    break;
            }
        }

        private static void GenerateData(ProductDescriptor descriptor)
        {
            Task.Factory.StartNew(async () =>
            {
                var scope = default(TransactionScope);
                var connection = default(DbConnection);
                try
                {
                    scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromMinutes(5) }, TransactionScopeAsyncFlowOption.Enabled);
                    connection = await SqlHelper.CreateConnectionAsync("DefaultConnection", true);

                    var employees = await BusinessHelper.RetrieveEmployeesAsync(connection);
                    var now = DateTimeOffset.Now;
                    var date = new DateTimeOffset(now.Year - 1, 1, 1, 0, 0, 0, 0, now.Offset);
                    var count = 0;
                    const int MaxCount = 105;
                    do
                    {
                        var random = new Random(date.GetHashCode());
                        var user = employees[random.Next(0, employees.Count - 1)];
                        var size = random.Next(1, 1000);
                        var type = random.Next(1, 4);
                        date = date.AddDays(random.Next(0, 10));
                        date = new DateTimeOffset(date.Year, date.Month, date.Day, random.Next(0, 23), random.Next(0, 59), random.Next(0, 59), random.Next(0, 999), date.Offset);

                        var id = await GenerateImportAsync(connection, descriptor, size, type, date, user);
                        if (count == MaxCount)
                        {
                            await descriptor.GenerateProductsDataAsync(connection, id, size, date, user);
                        }

                        ++count;
                    } while (count <= MaxCount);

                    scope.Complete();

                    Application.Current.Dispatcher.Invoke(new Action(() =>
                    {
                        MessageBox.Show(Application.Current.MainWindow, "Generate data completed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information, MessageBoxResult.OK, MessageBoxOptions.None);
                    }));
                }
                catch (Exception ex)
                {
                    Application.Current.Dispatcher.Invoke(new Action(() =>
                    {
                        MessageBox.Show(Application.Current.MainWindow, string.Format("{1}{0}{2}", Environment.NewLine, ex.Message, ex.StackTrace), "Error", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK, MessageBoxOptions.None);
                    }));
                }
                finally
                {
                    if (connection != null) connection.Dispose();
                    if (scope != null) scope.Dispose();
                }
            });
        }

        private static void GenerateFile(ProductDescriptor descriptor)
        {
            Task.Factory.StartNew(async () =>
            {
                var textWriter = default(TextWriter);
                var csvWriter = default(CsvWriter);
                try
                {
                    var fileName = string.Format(descriptor.FileNameTemplate, DateTime.Now.ToString("yyyyMMddhhmmss"));
                    fileName = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), fileName);
                    textWriter = new StreamWriter(fileName, false, Encoding.Unicode);
                    csvWriter = new CsvWriter(textWriter, new CsvConfiguration()
                    {
                        Delimiter = ",",
                        HasHeaderRecord = true,
                        IgnoreHeaderWhiteSpace = false,
                        Quote = '\"',
                        QuoteAllFields = false,
                        QuoteNoFields = false,
                        ThrowOnBadData = true,
                        TrimFields = true,
                        Encoding = Encoding.Unicode,
                    });
                    descriptor.GenerateProductsFile(descriptor, csvWriter);
                    await textWriter.FlushAsync();

                    Application.Current.Dispatcher.Invoke(new Action(() =>
                    {
                        MessageBox.Show(Application.Current.MainWindow, "Generate file completed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information, MessageBoxResult.OK, MessageBoxOptions.None);
                    }));
                }
                catch (Exception ex)
                {
                    Application.Current.Dispatcher.Invoke(new Action(() =>
                    {
                        MessageBox.Show(Application.Current.MainWindow, string.Format("{1}{0}{2}", Environment.NewLine, ex.Message, ex.StackTrace), "Error", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK, MessageBoxOptions.None);
                    }));
                }
                finally
                {
                    if (csvWriter != null) csvWriter.Dispose();
                    if (textWriter != null) textWriter.Dispose();
                }
            });
        }

        private static async Task GenerateDiamondsDataAsync(DbConnection connection, Guid id, int size, DateTimeOffset date, string user)
        {
            for (int i = 0; i < size; i++)
            {
                var random = new Random(date.GetHashCode() + i);
                await connection.CreateCommand("INSERT INTO [tc_product_diamond]([id],[type],[report_type],[report_number],[shape],[caret],[clarity],[color],[colored_color],[cut],[polish],[symmetry],[fluorescence],[comment],[index],[import_id],[vendor],[status],[cost],[sale_price],[created],[created_by],[modified],[modified_by])" +
                    "VALUES(@id, @type, @report_type, @report_number, @shape, @caret, @clarity, @color, @colored_color, @cut, @polish, @symmetry, @fluorescence, @comment, @index, @import_id, @vendor, @status, @cost, @sale_price, @created, @created_by, @modified, @modified_by)")
                    .AddParameter("@id", DbType.Guid, 0, Guid.NewGuid())
                    .AddParameter("@type", DbType.Int16, 0, random.Next(1, diamondTypes.Count))
                    .AddParameter("@report_type", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondReports.Count))
                    .AddParameter("@report_number", DbType.String, 128, string.Concat(date.ToString("yyyyMMddhhmmssffffff"), date.GetHashCode(), i))
                    .AddParameter("@shape", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondShapes.Count))
                    .AddParameter("@caret", DbType.Decimal, 18, 2, decimal.Parse((random.NextDouble() * 10).ToString("0.00")))
                    .AddParameter("@clarity", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondClarities.Count))
                    .AddParameter("@color", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondColors.Count))
                    .AddParameter("@colored_color", DbType.String, 128, diamondColoredColors[random.Next(0, diamondColoredColors.Count - 1)])
                    .AddParameter("@cut", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondCuts.Count))
                    .AddParameter("@polish", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondPolishes.Count))
                    .AddParameter("@symmetry", DbType.Int16, 0, random.Next() % 5 == 0 ? (object)DBNull.Value : random.Next(1, diamondSymmetries.Count))
                    .AddParameter("@fluorescence", DbType.String, 128, "None")
                    .AddParameter("@comment", DbType.String, 512, DBNull.Value)
                    .AddParameter("@index", DbType.Int32, 0, i + 1)
                    .AddParameter("@import_id", DbType.Guid, 0, id)
                    .AddParameter("@vendor", DbType.String, 64, DBNull.Value)
                    .AddParameter("@status", DbType.Int16, 0, random.Next(1, 3))
                    .AddParameter("@cost", DbType.Currency, 0, random.NextDouble() * random.Next(10000, 99999))
                    .AddParameter("@sale_price", DbType.Currency, 0, random.NextDouble() * random.Next(100000, 999999))
                    .AddParameter("@created", DbType.DateTimeOffset, 0, date)
                    .AddParameter("@created_by", DbType.String, 64, user)
                    .AddParameter("@modified", DbType.DateTimeOffset, 0, date)
                    .AddParameter("@modified_by", DbType.String, 64, user)
                    .ExecuteNonQueryAsync();
            }
        }

        private static void GenerateDiamondsFile(ProductDescriptor descriptor, CsvWriter writer)
        {
            writer.WriteField("序号,类型,形状,成本,销售价格,供应商,证书类型,证书编号,重量,净度,颜色,彩钻颜色,切工,抛光,对称,荧光,备注");
            writer.NextRecord();

            var random = new Random(descriptor.FileNameTemplate.GetHashCode() + DateTime.UtcNow.Millisecond);
            var size = random.Next(1, 1000);
            for (int i = 0; i < size; i++)
            {
                var type = diamondTypes[random.Next(0, diamondTypes.Count - 1)];
                var date = DateTime.Now;
                date = new DateTime(date.Year, random.Next(1, 12), random.Next(1, 27), random.Next(0, 23), random.Next(0, 59), random.Next(0, 59), random.Next(0, 999));
                writer.WriteField(i + 1);
                writer.WriteField(type);
                writer.WriteField(random.Next() % 5 == 0 ? string.Empty : diamondShapes[random.Next(0, diamondShapes.Count - 1)]);
                writer.WriteField(random.NextDouble() * random.Next(10000, 99999));
                writer.WriteField(random.NextDouble() * random.Next(100000, 999999));
                writer.WriteField(string.Empty);
                writer.WriteField(random.Next() % 5 == 0 ? string.Empty : diamondReports[random.Next(0, diamondReports.Count - 1)]);
                writer.WriteField(string.Concat(date.ToString("yyyyMMddhhmmssffffff"), date.GetHashCode()));
                writer.WriteField(decimal.Parse((random.NextDouble() * 10).ToString("0.00")));
                writer.WriteField(random.Next() % 5 == 0 ? string.Empty : diamondClarities[random.Next(0, diamondClarities.Count - 1)]);
                writer.WriteField(type == diamondTypes[0] ? (random.Next() % 5 == 0 ? string.Empty : diamondColors[random.Next(0, diamondColors.Count - 1)]) : string.Empty);
                writer.WriteField(type == diamondTypes[1] ? (random.Next() % 5 == 0 ? string.Empty : diamondColoredColors[random.Next(0, diamondColoredColors.Count - 1)]) : string.Empty);
                writer.WriteField(random.Next() % 5 == 0 ? string.Empty : diamondCuts[random.Next(0, diamondCuts.Count - 1)]);
                writer.WriteField(random.Next() % 5 == 0 ? string.Empty : diamondPolishes[random.Next(0, diamondPolishes.Count - 1)]);
                writer.WriteField(random.Next() % 5 == 0 ? string.Empty : diamondSymmetries[random.Next(0, diamondSymmetries.Count - 1)]);
                writer.WriteField("None");
                writer.WriteField(string.Empty);
                writer.NextRecord();
            }
        }

        private static async Task<Guid> GenerateImportAsync(DbConnection connection, ProductDescriptor descriptor, int size, int type, DateTimeOffset date, string user)
        {
            var sql = $"INSERT INTO[{descriptor.ImportTableName}]([id],[count],[type],[created],[created_by],[modified],[modified_by])VALUES(@id, @count, @type, @created, @created_by, @modified, @modified_by)";
            var id = Guid.NewGuid();
            await connection.CreateCommand(sql)
                .AddParameter("@id", DbType.Guid, 0, id)
                .AddParameter("@count", DbType.Int32, 0, size)
                .AddParameter("@type", DbType.Int32, 0, type)
                .AddParameter("@created", DbType.DateTimeOffset, 0, date)
                .AddParameter("@created_by", DbType.String, 64, user)
                .AddParameter("@modified", DbType.DateTimeOffset, 0, date)
                .AddParameter("@modified_by", DbType.String, 64, user)
                .ExecuteNonQueryAsync();
            return id;
        }

        private class ProductDescriptor
        {
            public string ImportTableName { get; set; }
            public string FileNameTemplate { get; set; }
            public Func<DbConnection, Guid, int, DateTimeOffset, string, Task> GenerateProductsDataAsync { get; set; }
            public Action<ProductDescriptor, CsvWriter> GenerateProductsFile { get; set; }
        }
    }
}
